import cx_Oracle
import os
import re

# 数据库连接信息
dsn = cx_Oracle.makedsn('host', 'port', service_name='service_name')
connection = cx_Oracle.connect(user='your_username', password='your_password', dsn=dsn)
cursor = connection.cursor()

username = 'TARGET_SCHEMA_NAME'
output_dir = username

if not os.path.exists(output_dir):
    os.makedirs(output_dir)

# 查询当前用户下所有表名
cursor.execute(f"SELECT table_name FROM all_tables WHERE owner = '{username}'")

tables = cursor.fetchall()


def oracle_to_mysql_ddl(oracle_ddl: str) -> str:
    # 移除 Oracle 特定关键字和存储参数
    mysql_ddl = re.sub(r'SEGMENT\s+CREATION\s+\w+', '', oracle_ddl, flags=re.IGNORECASE)
    mysql_ddl = re.sub(r'PCTFREE\s+\d+', '', mysql_ddl, flags=re.IGNORECASE)
    mysql_ddl = re.sub(r'INITRANS\s+\d+', '', mysql_ddl, flags=re.IGNORECASE)
    mysql_ddl = re.sub(r'MAXTRANS\s+\d+', '', mysql_ddl, flags=re.IGNORECASE)
    mysql_ddl = re.sub(r'TABLESPACE\s+\w+', '', mysql_ddl, flags=re.IGNORECASE)
    mysql_ddl = re.sub(r'STORE IN \(.*?\)', '', mysql_ddl, flags=re.IGNORECASE)
    mysql_ddl = re.sub(r'LOGGING', '', mysql_ddl, flags=re.IGNORECASE)

    # 替换数据类型
    mysql_ddl = mysql_ddl.replace("VARCHAR2", "VARCHAR")
    mysql_ddl = mysql_ddl.replace("NUMBER(", "DECIMAL(")
    mysql_ddl = mysql_ddl.replace("DATE", "DATE")
    mysql_ddl = mysql_ddl.replace("TIMESTAMP", "DATETIME")
    mysql_ddl = mysql_ddl.replace("CLOB", "TEXT")
    mysql_ddl = mysql_ddl.replace("BLOB", "BLOB")

    # 替换双引号为反引号
    mysql_ddl = mysql_ddl.replace('"', '`')

    return mysql_ddl


for table in tables:
    table_name = table[0]
    print(f"Exporting structure for {table_name}...")

    cursor.execute(f"""
        SELECT DBMS_METADATA.GET_DDL('TABLE', '{table_name}', '{username}') FROM dual
    """)
    ddl, = cursor.fetchone()
    oracle_sql = ddl.read()

    # 转换为 MySQL DDL
    mysql_sql = oracle_to_mysql_ddl(oracle_sql)

    # 写入文件
    with open(os.path.join(output_dir, f"{table_name}.sql"), 'w', encoding='utf-8') as f:
        f.write(mysql_sql)

print("导出并转换完成！")
cursor.close()
connection.close()
